PL/SQL Best Practices

A blog after many years 🙂

My best practices for PL/SQL are below:

• Always use Exception Block within each block, function, procedure
• Send email to your development team for any error which occurs in Production
• Give top priority to error email
• Make sure you will never get any error email 🙂
• Write reusable procedures/functions and SQL (If you use APEX you can use List of values)
• Avoid using Triggers except audit
• Make sure you add below 6 columns to important tables for Audit.

1. 1. Created by User
   2. Creation Date
   3. Updated by User
   4. Update Date
   5. User Agent
   6. IP Address

• Avoid using Stateful Package i.e. do not use package level variables/constants and instead create a DB table which stores all constants. This will avoid causing “ORA-06508: PL/SQL: could not find program unit being called” if you need to do a minor change in package then this could heavily affect where the application has thousands of users.

Thanks to Steven Feuerstein.

Sohilkumar Bhavsar

Cloud Architect at Zero Integration Ltd.

14+ years of experience with Oracle, Linux, Networking, Data Centre and Public Clouds. Played major role in development, implementation and management of SaaS CRM using AI and ML. Integrated 15+ Client CRM across United Kingdom and Australia.